RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / BulkOperations / Importers /
Filename : TasksImporter.php
back
<?php

namespace App\V2\BulkOperations\Importers;

use App\Libraries\FileStorageSystem;
use App\V2\BulkOperations\ImportFactories\FieldValidatorFactory;
use App\V2\BulkOperations\ImportFields\DataImportField;
use App\V2\BulkOperations\ImportFieldTransformers\DownloadImageFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\PasswordHashFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\RoleIdFieldTransformer;
use App\V2\BulkOperations\ImportServices\ImportLoggingService;
use App\V2\BulkOperations\ImportServices\TempTableService;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class TasksImporter extends BaseImporter
{
    private $fields = [];
    private $additional_fields = [];

    public function __construct($importProfile)
    {
        parent::__construct($importProfile);
    }

    private function populateFields(){
        //columns for tasks table:
        //task_title, description, goal_name, task_sp, task_type, product_uuid, due_by, is_repeated, is_featured, task_status

        //columns for tasks settings:
        //visible_to, review_required, repeate_after_days, task_review_allowed_to_type,

        //columns for task_images:
        //task_image_url

        //columns for task_visibility:
        //entity_type, entity_value

        //columns for tasks_review_allowed:
        //review_allowed_to_type

        //tasks table related fields
        $this->fields['task_title'] = new DataImportField('task_title','task_title', 'VARCHAR(255)',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['description'] = new DataImportField('description','description', 'TEXT',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['goal_name'] = new DataImportField('goal_name','goal_name', 'VARCHAR(255)',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['task_sp'] = new DataImportField('task_sp','task_sp', 'INT',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['product_uuid'] = new DataImportField('product_uuid','product_uuid', 'VARCHAR(255)',
            []);
        $this->fields['due_by'] = new DataImportField('due_by','due_by', 'DATETIME',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::FUTURE_DATE_VALIDATOR)]);
        $this->fields['is_featured'] = new DataImportField('is_featured','is_featured', 'BOOLEAN',[]);

        $this->fields['task_image_url'] = new DataImportField('task_image_url','task_image_url', 'TEXT',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_URL),]);
        $this->fields['local_task_image_url'] = new DataImportField('local_task_image_url', 'local_task_image_url', 'TEXT', []);

        $this->fields['is_repeated'] = new DataImportField('is_repeated','is_repeated', 'BOOLEAN',
        [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['repeate_after_days'] = new DataImportField('repeate_after_days','repeate_after_days', 'INT',[]);

        $this->fields['visible_to'] = new DataImportField('visible_to','visible_to', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::TASK_VISIBLE_TO_VALIDATOR)
        ]);
        $this->fields['visible_to_country'] = new DataImportField('visible_to_country','visible_to_country', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::COUNTRY_NAME_VALIDATOR)
        ]);
        $this->fields['visible_to_zipcode'] = new DataImportField('visible_to_zipcode','visible_to_zipcode', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::ZIPCODE_VALIDATOR)
        ]);

        $this->fields['visible_to_user_uuid'] = new DataImportField('visible_to_user_uuid','visible_to_user_uuid', 'TEXT',[]);
        $this->fields['visible_to_user_id'] = new DataImportField('visible_to_user_id','visible_to_user_id', 'TEXT',[]);

        $this->fields['review_required'] = new DataImportField('review_required','review_required', 'BOOLEAN',[]);
        $this->fields['review_allowed_to_type'] = new DataImportField('review_allowed_to_type','review_allowed_to_type', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::TASK_REVIEW_ALLOWED_TO_VALIDATOR)
        ]);

        $this->fields['created_by'] = new DataImportField('created_by','created_by', 'INT',[]);
        $this->fields['task_status'] = new DataImportField('task_status','task_status', 'VARCHAR(255)',[]);
        $this->fields['group_uuid'] = new DataImportField('group_uuid', 'group_uuid', 'VARCHAR(255)', []);

        //other fields to be populated with default value or with transformed values:
        $this->fields['task_type'] = new DataImportField('task_type', 'task_type', 'VARCHAR(255)', []);
        $this->fields['task_uuid'] = new DataImportField('task_uuid', 'task_uuid', 'VARCHAR(255)', []);
        $this->fields['product_id'] = new DataImportField('product_id', 'product_id', 'INT', []);

        $this->fields['task_title']->setIsRequired(true);
        $this->fields['description']->setIsRequired(true);
        $this->fields['goal_name']->setIsRequired(true);
        $this->fields['due_by']->setIsRequired(true);
        $this->fields['is_featured']->setIsRequired(true);

        $this->fields['product_id']->setDefaultValue(null);
        $this->fields['created_by']->setDefaultValue(-1);
        $this->fields['task_status']->setDefaultValue("'ACTIVE'");


        $this->fields['task_uuid']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_STR_UUID);
        $this->fields['local_task_image_url']->setTransformer(
            new DownloadImageFieldTransformer($this->fields['local_task_image_url'],
            [$this->fields['task_image_url']], 'task_images',
            'default-task-image-color.jpg') );

    }

    private function populateProfileSpecificAdditionalFields(){
        $this->additional_fields['task_id'] = new DataImportField('task_id', 'task_id', 'INT', []);
        $this->additional_fields['task_settings_id'] = new DataImportField('task_settings_id', 'task_settings_id', 'INT', []);
        $this->additional_fields['goal_id'] = new DataImportField('goal_id', 'goal_id', 'INT', []);
        $this->additional_fields['group_id'] = new DataImportField('group_id', 'group_id', 'INT', []);
        $this->additional_fields['team_id'] = new DataImportField('team_id', 'team_id', 'INT', []);

    }

    protected function getFields(): array {
        if(count($this->fields) == 0)
            $this->populateFields();
        return $this->fields;
    }

    protected function getProfileSpecificAdditionalFields(): array
    {
        if(count($this->additional_fields) == 0)
            $this->populateProfileSpecificAdditionalFields();
        return $this->additional_fields;
    }

    protected function getMainTableId(){
        return 'task_id';
    }

    protected function validateProfileSpecificData()
    {

        //1, populate user id for visible to type = PARENT or selected users
        $this->populateUserIdToTempTable();

        // 2, verify product_uuid and populate product_id
        $this->validateProduct();
        $this->populateProductId();

        // 3, validate goal and populate goal id
        $this->validateGoal();
        $this->populateGoalId();
        $this->populateTaskSpFromGoal();

        // 4, validate group and populate group id
        $this->validateGroupIfPresent();
        $this->populateGroupId();

        // 5, populate default values for few fields
        $this->populateTaskType();
    }

    public function importToMainProfileSpecificData()
    {
        //Insert into main tables:
        // 1, Insert into tasks table
        $this->insertIntoTasksTable();
        $this->populateTaskIdToTempTable();

        // 2, insert into task_images
        $this->insertIntoTaskImagesTable();

        // 3, insert into task_settings table
        $this->insertIntoTaskSettingsTable();
        $this->populateTaskSettingsIdToTempTable();

        // 4, insert into task_visibility
        $this->insertIntoTaskVisibilityTable();

        // 5, insert into task_review_allowed
        $this->insertIntoTaskReviewAllowedTable();
    }

    protected function validateProduct(){
        $field = $this->getAllFields()['product_uuid'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "{$field->getColumnName()} not found";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN products AS p ";
        $validationQuery .= " ON p.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE tmp.{$field->getColumnName()} IS NOT NULL "
            ." AND p.product_id IS NULL AND p.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }


    protected function populateProductId(){
        $field = $this->getAllFields()['product_uuid'];
        $ref_id_field = $this->getAllFields()['product_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `products` AS p  ";
        $validationQuery .= " ON p.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = p.product_id ";
        $validationQuery .= " WHERE tmp.{$field->getColumnName()} IS NOT NULL "
                        ." AND p.product_id IS NOT NULL AND p.deleted_at IS NULL "
                        ." AND tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateTaskSpFromGoal(){
        $field = $this->getAllFields()['goal_id'];
        $ref_id_field = $this->getAllFields()['task_sp'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN goals AS g ";
        $validationQuery .= " ON g.goal_id = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = g.scholarship_points ";
        $validationQuery .= " WHERE tmp.{$ref_id_field->getColumnName()} IS NULL "
                            ." AND g.goal_id IS NOT NULL AND g.goal_id IS NULL"
                            ." AND tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateTaskType(){
        $field = $this->getAllFields()['task_type'];
        $product_id_field = $this->getAllFields()['product_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " SET tmp.{$field->getColumnName()} = "
            ." CASE WHEN {$product_id_field->getColumnName()} IS NOT NULL "
            ." THEN 'ADDITIONAL_REWARD' ELSE 'SCHOLARSHIP_POINTS' END ";
        $validationQuery .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoTasksTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `tasks`(uuid, group_id, task_title, description, goal_id, "
            . " task_sp, task_type, product_id, due_by, is_repeated, created_by, task_status, "
            . " is_featured, created_at, import_queue_item_id) ";
        $select_sql = " SELECT task_uuid, group_id, task_title, description, goal_id, "
            . " task_sp, task_type, product_id, due_by, is_repeated, created_by, task_status, "
            . " is_featured, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateTaskIdToTempTable(){
        $field = $this->getAllFields()['task_uuid'];
        $ref_id_field = $this->getAllFields()['task_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `tasks` AS t  ";
        $validationQuery .= " ON t.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = t.task_id ";
        $validationQuery .= " WHERE  tmp.{$validate_is_valid_field_name} = 1 ";
        $validationQuery .= " AND t.task_id IS NOT NULL AND t.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoTaskImagesTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `task_images`(task_id, image_name, created_at, import_queue_item_id) ";
        $select_sql = " SELECT task_id, local_task_image_url, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    private function insertIntoTaskSettingsTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `task_settings`(task_id, review_required, repeate_after_days, "
                        ." created_at, import_queue_item_id, visible_to, task_review_allowed_to_type) ";
        $select_sql = " SELECT task_id, review_required, repeate_after_days, "
            ." NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id, "
            ." CASE WHEN LOCATE(',', visible_to) > 0 THEN SUBSTRING(visible_to, 1, LOCATE(',', visible_to) - 1)"
            ." ELSE visible_to END AS visible_to , "
            ." CASE WHEN LOCATE(',', review_allowed_to_type) > 0 THEN SUBSTRING(review_allowed_to_type, 1, LOCATE(',', review_allowed_to_type) - 1)"
            ." ELSE review_allowed_to_type END AS review_allowed_to_type  "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateTaskSettingsIdToTempTable(){
        $field = $this->getAllFields()['task_id'];
        $ref_id_field = $this->getAllFields()['task_settings_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `task_settings` AS t  ";
        $validationQuery .= " ON t.task_id = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = t.task_settings_id ";
        $validationQuery .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";
        $validationQuery .= " AND t.task_settings_id IS NOT NULL AND t.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoTaskVisibilityTable(){
        $task_visibility_type = ['ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD'
        ,'SELECTED_PARENT','COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD'];
        $visible_to_field = $this->getAllFields()['visible_to'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        foreach($task_visibility_type AS $visibility_type){
            $visible_to_type = $this->getVisibleToType($visibility_type);
            $visible_to_value_column = $this->getVisibleToValueColumn($visibility_type);

            $insert_sql = "INSERT INTO `task_visibility`(task_settings_id, entity_type, entity_value, created_at) ";
            $select_sql = " SELECT task_settings_id, '$visible_to_type' AS entity_type, $visible_to_value_column AS entity_value, NOW() "
                . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
            $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
                ."AND tmp.{$visible_to_field->getColumnName()} = '$visibility_type'";

            $total_records = $this->importQueueItem->additionalDetails->total_records;
            $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );

            if(in_array($visibility_type, ['ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD'])){
                $visible_to_type='COUNTRY_SINGLE';
                $visible_to_value_column = $this->getVisibleToValueColumn($visible_to_type);

                $insert_sql = "INSERT INTO `task_visibility`(task_settings_id, entity_type, entity_value, created_at) ";
                $select_sql = " SELECT task_settings_id, '$visible_to_type' AS entity_type, $visible_to_value_column AS entity_value, NOW() "
                    . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
                $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
                ."AND tmp.{$visible_to_field->getColumnName()} = '$visibility_type'";

                $total_records = $this->importQueueItem->additionalDetails->total_records;
                $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
            }
        }
    }

    private function getVisibleToType($visible_to_type){
        switch($visible_to_type){
            case 'ZIPCODE_PARENT':
            case 'ZIPCODE_PARENT_AND_CHILD':
                return 'ZIPCODE';
            case 'COUNTRY_PARENT':
            case 'COUNTRY_PARENT_AND_CHILD':
                return 'COUNTRY';
            case 'SELECTED_PARENT':
                return 'USER';
            case 'COUNTRY_SINGLE':
                return 'COUNTRY_SINGLE';
        }
        return '';
    }

    private function getVisibleToValueColumn($visible_to_type){
        switch($visible_to_type){
            case 'ZIPCODE_PARENT':
            case 'ZIPCODE_PARENT_AND_CHILD':
                return 'visible_to_zipcode';
            case 'COUNTRY_PARENT':
            case 'COUNTRY_PARENT_AND_CHILD':
                return 'visible_to_country';
            case 'SELECTED_PARENT':
                return 'visible_to_user_id';
            case 'COUNTRY_SINGLE':
                return 'visible_to_country';
        }
        return '';
    }

    private function insertIntoTaskReviewAllowedTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `task_review_allowed`(task_id, task_settings_id, review_allowed_to_type, created_at) ";
        $select_sql = " SELECT task_id, task_settings_id, review_allowed_to_type, NOW() "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
                    . " AND tmp.review_allowed_to_type IS NOT NULL";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateUserIdToTempTable(){
        $field = $this->getAllFields()['visible_to_user_uuid'];
        $ref_id_field = $this->getAllFields()['visible_to_user_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `users` AS u  ";
        $validationQuery .= " ON u.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = u.user_id ";
        $validationQuery .= " WHERE tmp.{$field->getColumnName()} IS NOT NULL AND u.user_id IS NOT NULL AND u.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

}

?>